/*

 * author : Mishal Parmar
 * Date: 22 Oct 2012
 */
package dbhandler;

import entityclasses.election;
import entityclasses.event;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

/**
 *
 * @author admin
 */
public class ElectionManage {
    
    
    public boolean addElectionquery(election newelection){
    
        String Query;
        
        
        java.util.Date dt = new java.util.Date();
        java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy");
        String currentTime = sdf.format(dt);
        int year=Integer.parseInt(currentTime);
               
        
        Query = "INSERT INTO `tbl_election`(`elect_id`, `elect_year`, `commissioner_id`, `start_date`, `end_date`,"
                + " `nominee_start`, `nominee_end`, `event_id`) VALUES (NULL,"+ year+ ","+ newelection.getCommissioner_id() +
                ",'"+ newelection.getStart_time() + "','"+ newelection.getEnd_time()+  "','"+ newelection.getNominee_start() +
                "','"+ newelection.getNominee_end() + "',"+ newelection.getElect_id()+");";
        
        //System.out.print(Query+"\n");
        DBConnection db = new DBConnection();
        db.connect();
        try {
            db.executeQuery(Query);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        db.disconnect();
        
        
        return true;
    }
    public boolean removeElectionquery(int electionid){
    
        String Query;
        
        
        java.util.Date dt = new java.util.Date();
        java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy");
        String currentTime = sdf.format(dt);
        int year=Integer.parseInt(currentTime);
               
        
        Query = "DELETE FROM `tbl_event` WHERE `event_id` IN (SELECT `event_id` FROM `tbl_election` WHERE `elect_id`="+electionid+")";
        
        //System.out.print(Query+"\n");
        DBConnection db = new DBConnection();
        db.connect();
        try {
            db.executeQuery(Query);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        db.disconnect();
        
        
        return true;
    }
     public boolean updateElectionquery(int electionid, election modifyelection){
    
        String Query;
        
        
        java.util.Date dt = new java.util.Date();
        java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy");
        String currentTime = sdf.format(dt);
        int year=Integer.parseInt(currentTime);
               
        
        Query = "UPDATE `tbl_election` SET `start_date`='"+ modifyelection.getStart_time() + "',"
                + "`end_date`='"+ modifyelection.getEnd_time() + "',"
                + "`nominee_start`='"+ modifyelection.getNominee_start() + "',"
                + "`nominee_end`='"+ modifyelection.getNominee_end() + "'"
                + " WHERE `elect_id`="+electionid;
        
        //System.out.print(Query+"\n");
        DBConnection db = new DBConnection();
        db.connect();
        try {
            db.executeQuery(Query);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        db.disconnect();
        
        
        return true;
    }
    public ArrayList viewelection(){
        
        
        ArrayList<election> election=new ArrayList<election>();
        
        String Query;
             
        Query = "select * from tbl_election;";
        
        DBConnection db = new DBConnection();
        ResultSet rs;
        
        Statement dbStatement;
        Connection dbCon;
        
        
        int ab = 0;
        db.connect();
        try {
            rs=db.customQuery(Query);
                      
            
           while(rs.next()){
                election elect=new election();
                elect.setElect_id(Integer.parseInt(rs.getString(1)));
                elect.setElect_year(Integer.parseInt(rs.getString(2)));
                elect.setCommissioner_id(Integer.parseInt(rs.getString(3)));
                elect.setStart_time(rs.getString(4));
                elect.setEnd_time(rs.getString(5));
                elect.setNominee_start(rs.getString(6));
                elect.setNominee_end(rs.getString(7));
                elect.setEvent_id(rs.getString(8));
                
                
                election.add(elect);
                
                
            }
            
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        db.disconnect();
        
        
        return election;
    }
    public ArrayList viewelectionbyid(int eid){
        
        
        ArrayList<election> election=new ArrayList<election>();
        
        String Query;
             
        Query = "select * from tbl_election where elect_id="+eid;
        
        DBConnection db = new DBConnection();
        ResultSet rs;
        
        
        int ab = 0;
        db.connect();
        try {
            rs=db.customQuery(Query);
                      
            
           while(rs.next()){
                election elect=new election();
                elect.setElect_id(Integer.parseInt(rs.getString(1)));
                elect.setElect_year(Integer.parseInt(rs.getString(2)));
                elect.setCommissioner_id(Integer.parseInt(rs.getString(3)));
                elect.setStart_time(rs.getString(4));
                elect.setEnd_time(rs.getString(5));
                elect.setNominee_start(rs.getString(6));
                elect.setNominee_end(rs.getString(7));
                elect.setEvent_id(rs.getString(8));
                
                
                election.add(elect);
                
                
            }
            
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        db.disconnect();
        
        
        return election;
    }
    
    public static void main(String [] args){
        
   /*
        String x;
        
        election e=new election();
        ElectionManage a=new ElectionManage();
        e.setStart_time("2012-10-01 12:00:00");
        e.setEnd_time("2012-10-14 06:00:00");
        e.setNominee_start("2012-10-23 10:00:00");
        e.setNominee_end("2012-10-24 20:00:00");
      //e.setEvent_id(3);
        e.setElect_year(2011);
        e.setCommissioner_id(36);
        
        System.out.print(a.addElectionquery(e));        */

        /*
        election e=new election();
        electionManage em=new electionManage();
        ArrayList arr=new ArrayList();
        arr=em.viewelectionbyid(2);
        e=(election)arr.get(0);
        System.out.println(e.getStart_time());
        System.out.println(e.getEnd_time());
        
        
        */
    }
            
}
